/*==============================================================*/
/* DBMS name:      ORACLE Version 10g                           */
/* Created on:     2015/6/15 星期一 14:50:53                       */
/*==============================================================*/

drop TRIGGER t_sys_authentication_info;
drop TRIGGER t_sys_organization_structure;
drop TRIGGER t_sys_resources;
drop TRIGGER t_sys_roles;
drop TRIGGER t_roles_resources;
drop TRIGGER t_sys_users;
drop TRIGGER t_sys_users_roles;
drop table sys_authentication_info cascade constraints;
drop table sys_organization_structure cascade constraints;
drop table sys_resources cascade constraints;
drop table sys_roles cascade constraints;
drop table sys_roles_resources cascade constraints;
drop table sys_users cascade constraints;
drop table sys_users_roles cascade constraints;
drop SEQUENCE pk_sys_authentication_info;
drop SEQUENCE pk_sys_organization_structure;
drop SEQUENCE pk_sys_resources;
drop SEQUENCE pk_sys_roles;
drop SEQUENCE pk_sys_roles_resources;
drop SEQUENCE pk_sys_users;
drop SEQUENCE pk_sys_users_roles;


/*==============================================================*/
/*创建序列用于主键自动增长                                      */
/*==============================================================*/

CREATE SEQUENCE pk_sys_authentication_info
INCREMENT BY 1   
START WITH 1 
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE SEQUENCE pk_sys_organization_structure
INCREMENT BY 1   
START WITH 1 
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE SEQUENCE pk_sys_resources
INCREMENT BY 1   
START WITH 1 
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE SEQUENCE pk_sys_roles
INCREMENT BY 1   
START WITH 1 
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE SEQUENCE pk_sys_roles_resources
INCREMENT BY 1   
START WITH 1 
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE SEQUENCE pk_sys_users
INCREMENT BY 1   
START WITH 1 
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE SEQUENCE pk_sys_users_roles
INCREMENT BY 1   
START WITH 1 
NOMAXVALUE
NOCYCLE
CACHE 10;


/*==============================================================*/
/* Table: "sys_authentication_info"                             */
/*==============================================================*/
create table sys_authentication_info  (
   "id"                 INTEGER                        default 0,
   "uid"                INTEGER                        default 0 not null,
   "account"            VARCHAR2(100)                   not null,
   "password"           VARCHAR2(100),
   "type"               VARCHAR2(20)                    not null,
   "status"             VARCHAR2(20)                    not null,
   "last_time"          DATE,
   "last_ip"            VARCHAR2(100),
   constraint PK_SYS_AUTHENTICATION_INFO primary key ("id")
);

/*==============================================================*/
/* Table: "sys_organization_structure"                          */
/*==============================================================*/
create table sys_organization_structure  (
   "os_id"              INTEGER                        default 0,
   "superior"           VARCHAR2(60),
   "full_name"          VARCHAR2(100),
   "name"               VARCHAR2(60),
   "status"             VARCHAR2(20),
   "creator"            INTEGER,
   "access_group"       VARCHAR2(40),
   "create_time"        DATE,
   "address"            VARCHAR2(200),
   "phone"              VARCHAR2(100),
   "head"               VARCHAR2(60),
   "contacts"           INTEGER,
   constraint PK_SYS_ORGANIZATION_STRUCTURE primary key ("os_id")
);

comment on table sys_organization_structure is
'组织结构';


/*==============================================================*/
/* Table: "sys_resources"                                       */
/*==============================================================*/
create table sys_resources  (
   "id"                 INTEGER                        default 0 not null,
   "text"               VARCHAR2(45),
   "type"               VARCHAR2(45),
   "priority"           INTEGER,
   "url"                VARCHAR2(100),
   "parentid"           INTEGER,
   "remark"             VARCHAR2(45),
   "status"             VARCHAR2(20),
   "icon"               VARCHAR2(100),
   constraint PK_SYS_RESOURCES primary key ("id")
);


/*==============================================================*/
/* Table: "sys_roles"                                           */
/*==============================================================*/
create table sys_roles  (
   "id"                 INTEGER                        default 0,
   "name"               VARCHAR2(45),
   "description"        VARCHAR2(100),
   "status"             VARCHAR2(20),
   constraint PK_SYS_ROLES primary key ("id")
);


/*==============================================================*/
/* Table: "sys_roles_resources"                                 */
/*==============================================================*/
create table sys_roles_resources  (
   "id"                 INTEGER                        default 0,
   "rid"                INTEGER,
   "resid"              INTEGER,
   "organization_id"    INTEGER,
   "status"             VARCHAR2(20),
   constraint PK_SYS_ROLES_RESOURCES primary key ("id")
);


/*==============================================================*/
/* Table: "sys_users"                                           */
/*==============================================================*/
create table sys_users  (
   "id"                 INTEGER                        default 0,
   "username"           VARCHAR2(45),
   "createtime"         DATE                           default to_date('2000-01-01,00:00:00','yyyy-mm-dd,hh24:mi:ss') not null,
   "status"             VARCHAR2(20),
   "last_login_time"    DATE,
   "last_login_ip"      VARCHAR2(100),
   "type"               VARCHAR2(20),
   "user_detail_id"     INTEGER,
   constraint PK_SYS_USERS primary key ("id")
);


/*==============================================================*/
/* Table: "sys_users_roles"                                     */
/*==============================================================*/
create table sys_users_roles  (
   "id"                 INTEGER                        default 0,
   "uid"                INTEGER,
   "rid"                INTEGER,
   "status"             VARCHAR2(20),
   constraint PK_SYS_USERS_ROLES primary key ("id")
);


/*==============================================================*/
/* 创建触发器用于主键自动增长                                   */
/*==============================================================*/
create trigger t_sys_authentication_info before insert on sys_authentication_info
for each row
begin
select pk_sys_authentication_info.nextval into :new."id" from dual;
end; 

create trigger t_sys_organization_structure before insert on sys_organization_structure
for each row
begin
select pk_sys_organization_structure.nextval into :new."os_id" from dual;
end; 

create trigger t_sys_resources before insert on sys_resources
for each row
begin
select pk_sys_resources.nextval into :new."id" from dual;
end; 

create trigger t_sys_roles before insert on sys_roles
for each row
begin
select pk_sys_roles.nextval into :new."id" from dual;
end; 

create trigger t_sys_roles_resources before insert on sys_roles_resources
for each row
begin
select pk_sys_roles_resources.nextval into :new."id" from dual;
end; 

create trigger t_sys_users before insert on sys_users
for each row
begin
select pk_sys_users.nextval into :new."id" from dual;
end; 

create trigger t_sys_users_roles before insert on sys_users_roles
for each row
begin
select pk_sys_users_roles.nextval into :new."id" from dual;
end; 

